#!/bin/sh

#hotel search and screening stat
#author taoran.xu
#date 2012-07-12

QDATE=`/bin/date -d 'last day' +%F`;

if [ $# -eq 1 ]; then
 QDATE="$1";

fi
QDATE2=`/bin/date -d "$QDATE" +%Y%m%d`;

DSQL=/export/tmp/dsql.sql;
BSQL=/export/tmp/bsql.sql;
SSQL=/export/tmp/ssql.sql;
ASQL=/export/tmp/asql.sql;
CSQL=/export/tmp/csql.sql;
LOGFILE=/export/tmp/listlog.log;

echo "`date '+%F %T'` BEGIN..." > $LOGFILE;
cat `ls /logs/qunarlog/hotel/*/$QDATE|grep  -v en` | awk -v qdate=$QDATE  -v dsql=$DSQL -v bsql=$BSQL -v ssql=$SSQL -v logfile=$LOGFILE  '
function urldecode(str,i,hex)
{
        for(i = 0; i < 10; i++)
        {
                hex[i] = i;
        }
        hex["A"] = hex["a"] = 10;
        hex["B"] = hex["b"] = 11;
        hex["C"] = hex["c"] = 12;
        hex["D"] = hex["d"] = 13;
        hex["E"] = hex["e"] = 14;
        hex["F"] = hex["f"] = 15;

        gsub(/\+/, " ");
        i = str;
	str = "";
        while(match(i, /%../))
        {
                if(RSTART > 1)
                {
                        str=str""sprintf("%s", substr(i, 1, RSTART-1));
                }
                str = str""sprintf("%c", hex[substr(i, RSTART+1, 1)] * 16 + hex[substr(i, RSTART+2, 1)]);
                i = substr(i, RSTART+RLENGTH);
        }
        return str""i;
}
	BEGIN{
		print "TRUNCATE TABLE hotel_list_search_new;" > ssql;
		print "TRUNCATE TABLE hotel_list_detail_new;" > dsql;
		print "TRUNCATE TABLE hotel_list_booking_new;" > bsql;
		print "awk bengin:",strftime("%D %T",systime()) >> logfile;
	}
	{
                if($7~/\/render\/renderAPI.jsp?/ && $7~/filterid/ && $7!~/ids/ && $7!~/bounds/) 
                {
                        split($7,arr,"?");
                        split(arr[2],brr,"&");
            
                        filterid="";
                        q="";
                        cityurl="";
                        distance="";
                        prs="";
                        levels="";
                        PF_5_VrVbqO="";
                        hotelType="";
                        
                        PF_0Ie44fNU="0";
                        PF_HGYGeXFY="0";
                        PF_dDjWmcqr="0";
                        PF_MMObDrW4="0";
                        PF_ownT_WG6="0";                                                                                                
                        PF_yYdMIL83="0";
                        PF_Y0LTFGFh="0";

			kdh="0";
			tuan="";
			PF_x0oSHP6u="";
			sort="";
			desc="";
			sortstr="0";    

                        for(x in brr)
                        {
                                split(brr[x],crr,"=");
				crr[2]=urldecode(crr[2]);
				#防止sql注入
				gsub(/\\*'\''/,"\\'\''",crr[2]);
                                gsub(/\\*\"/,"\\\"",crr[2]);

                                if(crr[1]=="filterid")
                                {
                                        filterid=crr[2];
                                }
                                else if(crr[1]=="q")
                                {
                                        q=crr[2];
                                }
                                else if(crr[1]=="cityurl")
                                {
                                        cityurl=crr[2];
                                }
                                else if(crr[1]=="distance")
                                {
                                        distance=crr[2];
                                }
                                else if(crr[1]=="prs")
                                {
                                        prs=crr[2];
                                }               
                                else if(crr[1]=="levels")
                                {
                                        levels=crr[2];
                                }
                                else if(crr[1]=="PF_5_VrVbqO")
                                {
                                        PF_5_VrVbqO=crr[2];
                                }
                                else if(crr[1]=="hotelType")
                                {
                                        hotelType=crr[2];
                                }
                                else if(crr[1]=="PF_HGYGeXFY")
                                {
                                        PF_HGYGeXFY="1";
                                }
                                else if(crr[1]=="PF_dDjWmcqr")
                                {
                                        PF_dDjWmcqr="1";
                                }       
                                else if(crr[1]=="PF_MMObDrW4")
                                {       
                                        PF_MMObDrW4="1";
                                }               
                                else if(crr[1]=="PF_ownT_WG6")
                                {
                                        PF_ownT_WG6="1";
                                }
                                else if(crr[1]=="PF_yYdMIL83")
                                {
                                        PF_yYdMIL83="1";
                                }
                                else if(crr[1]=="PF_Y0LTFGFh")
                                {
                                        PF_Y0LTFGFh="1";
                                }      
				else if(crr[1]=="kdh")
                                {
                                        kdh="1";
                                }      
				else if(crr[1]=="tuan")
                                {
                                        tuan=crr[2];
                                }      
                    		else if(crr[1]=="PF_x0oSHP6u")
                                {
                                        PF_x0oSHP6u=crr[2];
                                }  
				else if(crr[1]=="sort")
                                {
                                        sort=crr[2];
                                }  
				else if(crr[1]=="desc")
                                {
                                        desc=crr[2];
                                }  
                    
                        }
			if(sort=="DISTANCE" && desc=="0")
			{
				sortstr = "1";
			}
			else if(sort=="DC" && desc=="1")
                        {
                                sortstr = "2";
                        }
			else if(sort=="DC" && desc=="O")
                        {
                                sortstr = "3";
                        }
			else if(sort=="SCORE" && desc=="1")
                        {
                                sortstr = "4";
                        }
			else if(sort=="PRICE" && desc=="1")
                        {
                                sortstr = "5";
                        }
			else if(sort=="PRICE" && desc=="0")
                        {
                                sortstr = "6";
                        }

                        print "insert  into hotel_list_search_new(reportdate,filterid,queryword,city,distance,prs,levels,hotelType,hotelbrand,PF_0Ie44fNU,PF_HGYGeXFY,PF_dDjWmcqr,PF_MMObDrW4,PF_ownT_WG6,PF_yYdMIL83,PF_Y0LTFGFh,PF_x0oSHP6u,kdh,tuan,sort) values ('\''"qdate"'\'','\''"filterid"'\'','\''"q"'\'','\''"cityurl"'\'','\''"distance"'\'','\''"prs"'\'','\''"levels"'\'','\''"hotelType"'\'','\''"PF_5_VrVbqO"'\'','\''"PF_0Ie44fNU"'\'','\''"PF_HGYGeXFY"'\'','\''"PF_dDjWmcqr"'\'','\''"PF_MMObDrW4"'\'','\''"PF_ownT_WG6"'\'','\''"PF_yYdMIL83"'\'','\''"PF_Y0LTFGFh"'\'','\''"PF_x0oSHP6u"'\'','\''"kdh"'\'','\''"tuan"'\'','\''"sortstr"'\'');" >> ssql;            
                }
		else if($7~/\/trans_d?/ && $7~/filterid/)
		{
			split($7,arr,"?");
			split(arr[2],brr,"&");
			filterid="";
			qptype="";
			for(x in brr)
			{
				split(brr[x],crr,"=");
				if(crr[1]=="filterid")
				{
					filterid=crr[2];
				}
				else if(crr[1]=="qptype")
				{
					crr[2]=urldecode(crr[2]);
					qptype=crr[2];
				}
			} 
			detailArr[filterid","qptype]++;
			
		}
		else if($7~/\/booksystem\/Booking_Main.jsp?/ && $7~/filterid/) 
		{
			split($7,arr,"?");
	    		split(arr[2],brr,"&");
	    		filterid="";
	    		for(x in brr)
	    		{
	            		split(brr[x],crr,"=");
	            		if(crr[1]=="filterid")
	            		{
	                    		filterid=crr[2];
	            		}
	    		} 
			bookingArr[filterid]++;			

		}
	}
	END{
		for(x in detailArr) 
		{
			split(x,tmpArr,",");
			print "insert into hotel_list_detail_new(reportdate,filterid,qptype,detail_cnt) values('\''"qdate"'\'','\''"tmpArr[1]"'\'','\''"tmpArr[2]"'\'','\''"detailArr[x]"'\'');" >> dsql;
		}
		for(x in bookingArr)
		{
			print "insert into hotel_list_booking_new(reportdate,filterid,booking_cnt) values('\''"qdate"'\'','\''"x"'\'','\''"bookingArr[x]"'\'');" >> bsql; 

		}
		print "awk end:",strftime("%D %T",systime()) >> logfile;
	}'    


zcat /logs/qunarlog/click/$QDATE2/*/*/click.log.gz | awk -v qdate=$QDATE  -v csql=$CSQL -F ' 
BEGIN{
	print "truncate table hotel_list_no;"> csql
}
{
	if($7=='200' && ($5~/a=%E6%97%A0%E7%BB%93%E6%9E%9C-YN/ ||$5~/a=%E6%97%A0%E7%BB%93%E6%9E%9C-NS/ ||$5~/a=%E6%97%A0%E7%BB%93%E6%9E%9C-YS/ ))
	{
		split($5,arr,"?");
    		split(arr[2],brr,"&");
    		filterid="";
   
   		 for(x in brr)
    		{
            		split(brr[x],crr,"=");
            		if(crr[1]=="b")
            		{
                    		filterid=crr[2];
                    		continue;
            		}
            
    		}
    		print "insert into hotel_list_no(reportdate,filterid) values('\''"qdate"'\'','\''"filterid"'\'');" >> csql;
	
	
	}
	
}'  

echo "delete h  from  hotel_list_search_new  as h, (select filterid from hotel_list_search_new group by filterid having  count(1)>1 ) as a where a.filterid=h.filterid;" >$ASQL;

echo "delete h  from  hotel_list_no  as h, (select filterid from hotel_list_no group by filterid having  count(1)>1 ) as a where a.filterid=h.filterid;" >>$ASQL;

echo "delete from hotel_list_new where reportdate='"$QDATE"';" >> $ASQL;

echo "INSERT  INTO hotel_list_new(reportdate,filterid,queryword,qptype,city,distance,prs,levels,hotelType,hotelbrand,PF_0Ie44fNU,PF_HGYGeXFY,PF_dDjWmcqr,PF_MMObDrW4,PF_ownT_WG6,PF_yYdMIL83,PF_Y0LTFGFh,PF_x0oSHP6u,kdh,tuan,sort,result,detail_cnt,booking_cnt) SELECT d.reportdate, d.filterid,queryword,d.qptype,city,distance,prs,levels,hotelType,hotelbrand,PF_0Ie44fNU,PF_HGYGeXFY,PF_dDjWmcqr,PF_MMObDrW4,PF_ownT_WG6,PF_yYdMIL83,PF_Y0LTFGFh,PF_x0oSHP6u,kdh,tuan,sort,'1',detail_cnt,0 FROM hotel_list_search_new s,hotel_list_detail_new d WHERE d.filterid=s.filterid  AND d.reportdate='"$QDATE"' and d.filterid<>'';" >> $ASQL;

echo "update hotel_list_new l, hotel_list_booking_new b set l.booking_cnt=b.booking_cnt where l.reportdate='"$QDATE"' and b.filterid=l.filterid;" >> $ASQL;

echo "INSERT  INTO hotel_list_new(reportdate,filterid,queryword,qptype,city,distance,prs,levels,hotelType,hotelbrand,PF_0Ie44fNU,PF_HGYGeXFY,PF_dDjWmcqr,PF_MMObDrW4,PF_ownT_WG6,PF_yYdMIL83,PF_Y0LTFGFh,PF_x0oSHP6u,kdh,tuan,sort,result,detail_cnt,booking_cnt) SELECT n.reportdate, n.filterid,queryword,'',city,distance,prs,levels,hotelType,hotelbrand,PF_0Ie44fNU,PF_HGYGeXFY,PF_dDjWmcqr,PF_MMObDrW4,PF_ownT_WG6,PF_yYdMIL83,PF_Y0LTFGFh,PF_x0oSHP6u,kdh,tuan,sort,'0',0,0 FROM hotel_list_search_new s,hotel_list_no n WHERE n.filterid=s.filterid  AND n.reportdate='"$QDATE"' and n.filterid<>'';" >> $ASQL;

mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8 < $SSQL;

mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8 < $BSQL;

mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8 < $DSQL;

mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8 < $CSQL;

mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8 < $ASQL;


echo "`date '+%F %T'` END..." >> $LOGFILE;
